package com.zb.dao.base;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.zb.entity.base.OmsCommonParam;
import com.zb.entity.exception.OmsDaoException;
import com.zb.util.Constants;
import com.zb.util.ReflectUtils;
import com.zb.util.annotation.AnnotationUtils;
/**
 * Dao层使用到的公共方法
 * @author huanghao
 *
 * @param <T>
 */
@Repository(value="base")
public class OmsCommonDao<T> extends BaseDao<T>
{
	
	private  Log log = LogFactory.getLog(OmsCommonDao.class); 
	
	public void logError(String errorMsg,Throwable error)
	{
		log.error(errorMsg, error);
	}
	
	public void logDebug(String msg)
	{
		log.debug(msg);
	}
	
	protected void checkParam(Object param)throws OmsDaoException
	{
		if (param == null)
		{
			throw new OmsDaoException("the param is null");
		}
	}
	
	protected void addValidFlagParam2Query(OmsCommonParam param)
	{
		ListOrderedMap queryParam = param.getParamsMap();
		if (queryParam == null)
		{
			queryParam = new ListOrderedMap();
			param.setParamsMap(queryParam);
		}
		queryParam.put(Constants.Jdbc.VALID_FLAG, OmsCommonParam.VALID_FLAG_ABLE);
	} 
	/**
	 *  查询该类对应的表的总数信息
	 * @param cls
	 * @return
	 * @throws OmsDaoException
	 */
	public Integer queryTotalByObject(final Class<?> cls,final ListOrderedMap paramsMap)throws OmsDaoException
	{
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		StringBuilder querySql = new StringBuilder();
		querySql.append("select count(1) from ").append(tableName);
		boolean firstAppend = true;		
		//循环拼接引用查询的映射列条件（链接查询条件）
		if(paramsMap!=null)
		{
			for(int i=0;i<paramsMap.size();i++)
			{
				if(firstAppend){
					firstAppend = false;
					querySql.append(" WHERE ");
				}
				else
				{
					querySql.append(" AND ");
				}
				//tableName（主表）表的条件
				querySql.append("`");
				querySql.append(paramsMap.get(i));
				querySql.append("`");
				querySql.append("=? ");
			}
		}
		
		if(Constants.Jdbc.SHOW_SQL)
		{
			logDebug("[MyJDBCFrameWork]:"+querySql.toString());
		}
		return this.getJdbcTemplate().query(querySql.toString(), new PreparedStatementSetter() 
		{
			@Override
			public void setValues(PreparedStatement arg0) throws SQLException 
			{
				int size = paramsMap.valueList().size();
				for (int i = 0; i < size; i++) 
				{
					arg0.setObject(i+1, paramsMap.valueList().get(i));
				}
			}
		}, new ResultSetExtractor<Integer>() 
		{

			@Override
			public Integer extractData(ResultSet arg0) throws SQLException, DataAccessException 
			{
				if (arg0.next())
				{
					return arg0.getInt(1);
				}
				return 0;
			}
		});
	}
	
	
	
	
	public int logicDeleteObjectBySinglePk(final Class<?> cls,final Object pkValue)
	{
		//得到表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		//开始拼接sql
		final StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(tableName);
		
		final Field[] fields = cls.getDeclaredFields();
		
		sql.append(" SET ");
		sql.append(Constants.Jdbc.VALID_FLAG);
		sql.append("=? ");
		boolean firstAppend = true;
		
		try {
			for (Field field : fields) {

				//判断是否含有不可用修饰符
				if(!ReflectUtils.judgeModifiersAvailable(field)){
					//得到主键注解  //排除序列id //排除引用对象
					if(!AnnotationUtils.ignore(field)
							&& AnnotationUtils.fieldIsPrimarkey(field)){
						//调用get方法
						
						
						if(firstAppend){
							firstAppend=false;
							sql.append(" WHERE ");
						}
						else{
							sql.append(" AND ");
						}
						sql.append("`");
						sql.append(field.getName());
						sql.append("`");
						//拼接字段占位
						sql.append("=? ");

					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}

		if(Constants.Jdbc.SHOW_SQL){
			logDebug("[MyJDBCFrameWork]:"+sql.toString());
		}		
		
		return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter()  {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					//有序设置参数集合的参数值
					ps.setObject(1, 0);
					ps.setObject(2, pkValue);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
	
	public void add(T oom)throws OmsDaoException
	{
		checkParam(oom);
		int flag =  0;
		try
		{
			flag = this.save(oom);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
		if (flag < 0)
		{
			throw new OmsDaoException("insert fail");
		}
	}
	
	/**
	 * 更新工厂信息
	 * @param obj
	 * @throws OmsDaoException
	 */
	public void update(T obj)throws OmsDaoException
	{
		checkParam(obj);
		int flag =  0;
		try
		{
			flag = this.updateObject(obj);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
		if (flag < 0)
		{
			throw new OmsDaoException("update fail");
		}
	}
	/**
	 * @param id
	 * @throws OmsDaoException
	 */
	public void deleteById(String id,Class<T> cls)throws OmsDaoException
	{
		checkParam(id);
		int flag =  0;
		try
		{
			flag = this.logicDeleteObjectBySinglePk(cls, id);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
		if (flag < 0)
		{
			throw new OmsDaoException("del fail");
		}
		
	}

	public  List<T> pagingFind(OmsCommonParam param,Class<T> cls)
			throws OmsDaoException
	{
		checkParam(param);
		addValidFlagParam2Query(param);
		try
		{
			return this.findListByClass(cls, param.getParamsMap(), param.getPageNo(),
				param.getPageSize(), param.getSortName(), param.getSortValue());
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
	}
	
	public  List<T> pagingFind(OmsCommonParam param,T obj)
			throws OmsDaoException
	{
		checkParam(param);
		addValidFlagParam2Query(param);
		try
		{
			return this.findExample(obj, param.getPageNo(), param.getPageSize(), param.getSortName(), param.getSortValue());
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
	}
	
	public  List<T> pagingFind(OmsCommonParam param,T obj,String tableName,final List<Object> queryParamList)
			throws OmsDaoException
	{
		checkParam(param);
		addValidFlagParam2Query(param);
		try
		{
			return this.findExampleByTableName(obj, param.getPageNo(), param.getPageSize(), param.getSortName(), param.getSortValue(),tableName,queryParamList);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e.getMessage(),e);
		}
	}
	
	@SuppressWarnings("unchecked")
	public  T findRecordByPK(T obj) throws OmsDaoException
	{
			checkParam(obj);
		//得到类信息
				Class<?> cls = obj.getClass();
				//所有属性
				Field fields[] =  cls.getDeclaredFields();
				//获取表名称
				String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
				
				StringBuffer buffer = new StringBuffer("SELECT ");
				buffer.append(ReflectUtils.setQueryColumn(fields, tableName));
				buffer.append(" FROM ");
				buffer.append(tableName);
				//是否首次追加条件
				boolean firstAppend = true;
				//参数值
				final List<Object> queryParamList = new ArrayList<Object>();
				//循环属性追加条件
				for (Field field : fields) {
					//判断是否含有不可用修饰符
					if(!ReflectUtils.judgeModifiersAvailable(field)){
						//是否为忽略映射属性
						if(!AnnotationUtils.ignore(field))
						{
							//调用get方法
							Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
							if(resultObj!=null && !"".equals(resultObj) && AnnotationUtils.fieldIsPrimarkey(field))
							{
								//首次追加where条件
								if(firstAppend){
									firstAppend = false;
									buffer.append(" WHERE ");
								}
								else
								{
									buffer.append(" AND ");
								}
								buffer.append("`");
								buffer.append(field.getName());
								buffer.append("`");
								buffer.append("= ?");
								//将参数值顺序保存至list
								queryParamList.add(resultObj);
							}
						}
					}
				}
				if(Constants.Jdbc.SHOW_SQL){
					System.out.println("[MyJDBCFrameWork]:"+buffer);
					System.out.println("[param]:"+queryParamList);
				}
				try
				{
					return getJdbcTemplate().queryForObject(buffer.toString(), new BeanPropertyRowMapper<T>((Class<T>) cls), 
							queryParamList.toArray(new Object[queryParamList.size()	]));
				}
				catch(EmptyResultDataAccessException e)
				{
					return null;
				}

	}
	
	/**
	 *  查询该类对应的表的总数信息
	 * @param obj
	 * @return
	 * @throws OmsDaoException
	 */
	public Integer queryTotalByObject(T obj)throws Exception
	{
		Class<?> cls = obj.getClass();
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		StringBuilder buffer = new StringBuilder();
		buffer.append("select count(1) from ").append(tableName);
		boolean firstAppend = true;		
		//参数值
		final List<Object> queryParamList = new ArrayList<Object>();
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) {
			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field))
				{
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj) )
					{
						//首次追加where条件
						if(firstAppend){
							firstAppend = false;
							buffer.append(" WHERE ");
						}
						else
						{
							buffer.append(" AND ");
						}
						if(AnnotationUtils.isQueryByLike(field))
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("like ?");
							StringBuilder likeValue = new StringBuilder();
							likeValue.append("%").append(resultObj).append("%");
							//将参数值顺序保存至list
							queryParamList.add(likeValue.toString());
						}
						else
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("=?");
							//将参数值顺序保存至list
							queryParamList.add(resultObj);
						}
					}
				}
			}
		}
		
		if(Constants.Jdbc.SHOW_SQL)
		{
			logDebug("[MyJDBCFrameWork]:"+buffer.toString());
		}
		return this.getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() 
		{
			@Override
			public void setValues(PreparedStatement arg0) throws SQLException 
			{
				int size =queryParamList.size();
				for (int i = 0; i < size; i++) 
				{
					arg0.setObject(i+1, queryParamList.get(i));
				}
			}
		}, new ResultSetExtractor<Integer>() 
		{

			@Override
			public Integer extractData(ResultSet arg0) throws SQLException, DataAccessException 
			{
				if (arg0.next())
				{
					return arg0.getInt(1);
				}
				return 0;
			}
		});
	}
	
	
	/**
	 * 
	 * @param obj
	 * @param tableName
	 * @param queryParamList
	 * @return
	 * @throws Exception
	 */
	public Integer queryTotalByObjectTblName(T obj,String tableName,final List<Object> queryParamList)throws Exception
	{
		Class<?> cls = obj.getClass();
		StringBuilder buffer = new StringBuilder();
		buffer.append("select count(1) from ").append(tableName);
		boolean firstAppend = true;		
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) {
			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field))
				{
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj) )
					{
						//首次追加where条件
						if(firstAppend){
							firstAppend = false;
							buffer.append(" WHERE ");
						}
						else
						{
							buffer.append(" AND ");
						}
						if(AnnotationUtils.isQueryByLike(field))
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("like ?");
							StringBuilder likeValue = new StringBuilder();
							likeValue.append("%").append(resultObj).append("%");
							//将参数值顺序保存至list
							queryParamList.add(likeValue.toString());
						}
						else
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("=?");
							//将参数值顺序保存至list
							queryParamList.add(resultObj);
						}
					}
				}
			}
		}
		
		if(Constants.Jdbc.SHOW_SQL)
		{
			logDebug("[MyJDBCFrameWork]:"+buffer.toString());
		}
		return this.getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() 
		{
			@Override
			public void setValues(PreparedStatement arg0) throws SQLException 
			{
				int size =queryParamList.size();
				for (int i = 0; i < size; i++) 
				{
					arg0.setObject(i+1, queryParamList.get(i));
				}
			}
		}, new ResultSetExtractor<Integer>() 
		{

			@Override
			public Integer extractData(ResultSet arg0) throws SQLException, DataAccessException 
			{
				if (arg0.next())
				{
					return arg0.getInt(1);
				}
				return 0;
			}
		});
	}
	
	/**
	 * 
	 * @param obj
	 * @param sql
	 * @param queryParamList
	 * @return
	 * @throws Exception
	 */
	public Integer queryTotalBySql(T obj,String sql,final List<Object> queryParamList)throws Exception
	{
		Class<?> cls = obj.getClass();
		StringBuilder buffer = new StringBuilder();
		buffer.append(sql);
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) {
			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field))
				{
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj) )
					{
						buffer.append(" AND ");
						if(AnnotationUtils.isQueryByLike(field))
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("like ?");
							StringBuilder likeValue = new StringBuilder();
							likeValue.append("%").append(resultObj).append("%");
							//将参数值顺序保存至list
							queryParamList.add(likeValue.toString());
						}
						else
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("=?");
							//将参数值顺序保存至list
							queryParamList.add(resultObj);
						}
					}
				}
			}
		}
		
		if(Constants.Jdbc.SHOW_SQL)
		{
			logDebug("[MyJDBCFrameWork]:"+buffer.toString());
		}
		return this.getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() 
		{
			@Override
			public void setValues(PreparedStatement arg0) throws SQLException 
			{
				int size =queryParamList.size();
				for (int i = 0; i < size; i++) 
				{
					arg0.setObject(i+1, queryParamList.get(i));
				}
			}
		}, new ResultSetExtractor<Integer>() 
		{

			@Override
			public Integer extractData(ResultSet arg0) throws SQLException, DataAccessException 
			{
				if (arg0.next())
				{
					return arg0.getInt(1);
				}
				return 0;
			}
		});
	}
	
	/**
	 * 获取最大
	 * @return
	 * @throws OmsDaoException
	 */
	public  String findMaxId(Class<T> cls)throws OmsDaoException
	{
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		StringBuilder buffer = new StringBuilder();
		buffer.append("select max(");
		boolean isFinded = false;
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) 
		{
			if (AnnotationUtils.fieldIsPrimarkey(field))
			{
				buffer.append(field.getName()).append(") from ").append(tableName);
				isFinded= true;
				break;
			}
		}
		
		if (isFinded)
		{
			try
			{
				return this.getJdbcTemplate().queryForObject(buffer.toString(), String.class);
			}
			catch(Exception e)
			{
				logError(e.getMessage(), e);
				throw new OmsDaoException(e);
			}
		}
		else
		{
			throw new OmsDaoException("donot find the entity pk annotaiton");
		}
		
	}
	
	
	public String findMaxPkIdBySingle(int rightIndex,Class<T> cls)throws OmsDaoException
	{
		//String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		//String sql = "select max(right(oper_merchant_id,13)) as maxId from oms_oper_merchant";
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		StringBuilder buffer = new StringBuilder();
		buffer.append("select max(right(");
		boolean isFinded = false;
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) 
		{
			if (AnnotationUtils.fieldIsPrimarkey(field))
			{
				buffer.append(field.getName()).append(",").append(rightIndex).
				append("))  as maxId from ").append(tableName);
				isFinded= true;
				break;
			}
		}
		
		if (isFinded)
		{
			try
			{
				return this.getJdbcTemplate().queryForObject(buffer.toString(), String.class);
			}
			catch(Exception e)
			{
				logError(e.getMessage(), e);
				throw new OmsDaoException(e);
			}
		}
		else
		{
			throw new OmsDaoException("donot find the entity pk annotaiton");
		}
	}
	
	
	public String findMaxPkIdByCondition(int beginIndex,int length,String condition,Class<T> cls)throws OmsDaoException
	{
		//String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		//String sql = "select max(right(oper_merchant_id,13)) as maxId from oms_oper_merchant";
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		StringBuilder buffer = new StringBuilder();
		buffer.append("select max(substring(");
		boolean isFinded = false;
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//循环属性追加条件
		for (Field field : fields) 
		{
			if (AnnotationUtils.fieldIsPrimarkey(field))
			{
				buffer.append(field.getName()).append(",").append(beginIndex).append(",").append(length).
				append("))  as maxId from ").append(tableName);
				isFinded= true;
				break;
			}
		}
		buffer.append(" "+condition);
		if (isFinded)
		{
			try
			{
				return this.getJdbcTemplate().queryForObject(buffer.toString(), String.class);
			}
			catch(Exception e)
			{
				logError(e.getMessage(), e);
				throw new OmsDaoException(e);
			}
		}
		else
		{
			throw new OmsDaoException("donot find the entity pk annotaiton");
		}
	}

}
